#!/usr/bin/python
# -*- coding: utf-8 -*-
#
# Licensed under GPL v3
# Copyright 2008, Ugur Cetin
#
# This program is free software; you can redistribute it and/or modify it under
# the terms of the GNU General Public License as published by the Free
# Software Foundation; either version 3 of the License, or (at your option)
# any later version.
#
# Please read the COPYING file.
#

import os, urllib

from BeautifulSoup import BeautifulSoup
from pysqlite2 import dbapi2 as sqlite

DATABASE_NAME = "iett.db"
DATABASE_SQL = "database.sql"

def cleaner(string):
    if not string:
        return string
    newstring = ""
    for character in string:
        if character in "0123456789":
            newstring += character
    return newstring

def main():
    #connect to database and create tables
    try:os.unlink(DATABASE_NAME)
    except:pass
    con = sqlite.connect(DATABASE_NAME)
    con.executescript(open(DATABASE_SQL).read())

    #get bus list
    html = urllib.urlopen("http://www.iett.gov.tr/").read()
    soup = BeautifulSoup(html)
    buslist = [i["value"] for i in soup("option") if i["value"] != ""]
    con.executemany("INSERT INTO busses(name) VALUES(?)", [(bus,) for bus in buslist])
    daytypes = (("Weekdays",),("Saturday",),("Sunday",))
    con.executemany("INSERT INTO daytypes(name) values(?)", daytypes)

    for bus in buslist:
        #get stops for bus
        print "getting stops for %s" % bus
        bus_id = con.execute("SELECT id FROM busses WHERE name=?", (bus,)).fetchone()[0]
        html = urllib.urlopen("http://www.iett.gov.tr/saat/orer.php?hid=hat&BtnDetay=3&hatcode=%s&a=q" % bus).read()
        soup = BeautifulSoup(html)

        bustype = soup("b")[1].string
        if bustype:
            con.execute("INSERT INTO  bustypes(name) VALUES(?)", (bustype,))
            bus_type_id = con.execute("SELECT id FROM bustypes WHERE name=?", (bustype,)).fetchone()[0]
            con.execute("UPDATE busses SET bus_type_id=? WHERE id=?", (bus_type_id, bus_id))
        stoplist = [(str(stop("td")[1].contents[0]).strip(), stop("td")[1].font.contents[0]) for stop in soup.findAll("tr", bgcolor=("#fef5e0", "#effcff"))]
        con.executemany("INSERT INTO locations(name) VALUES(?)", [(i[1],) for i in stoplist])
        for i in range(len(stoplist)):
            location_id = con.execute("SELECT id FROM locations WHERE name=?", (stoplist[i][1],)).fetchone()[0]
            exists = con.execute("SELECT id FROM stops WHERE location_id=? AND name=?", (location_id, stoplist[i][0])).fetchone()
            if not exists:
                con.execute("INSERT INTO stops(location_id, name) VALUES(?,?)", (location_id ,stoplist[i][0]))
            stop_id = con.execute("SELECT id FROM stops WHERE location_id=? AND name=?", (location_id, stoplist[i][0])).fetchone()[0]
            con.execute("INSERT INTO busstops(stop_id, bus_id, sequence) VALUES(?,?,?)", (stop_id, bus_id, i))

        #get hours for bus
        print "getting times for %s" % bus
        html = urllib.urlopen("http://www.iett.gov.tr/saat.php?hat=%s" % bus).read()
        soup = BeautifulSoup(html)

        try:
            firststop_id = con.execute("SELECT stop_id FROM busstops WHERE bus_id=? ORDER BY sequence ASC", (bus_id,)).fetchone()[0]
            laststop_id = con.execute("SELECT stop_id FROM busstops WHERE bus_id=? ORDER BY sequence DESC", (bus_id,)).fetchone()[0]
        except TypeError:
            pass #bus has no stops

        for i in soup.table.table("tr")[3:]:
            row = [cleaner(x.string) for x in i("font")]
            if row[0] is not None:
                con.execute("INSERT INTO times(bus_id, time, daytype_id, stop_id) VALUES(?,?,?,?)", (bus_id, row[0], 1, firststop_id))
            if row[1] is not None:
                con.execute("INSERT INTO times(bus_id, time, daytype_id, stop_id) VALUES(?,?,?,?)", (bus_id, row[1], 1, laststop_id))
            if row[2] is not None:
                con.execute("INSERT INTO times(bus_id, time, daytype_id, stop_id) VALUES(?,?,?,?)", (bus_id, row[2], 2, firststop_id))
            if row[3] is not None:
                con.execute("INSERT INTO times(bus_id, time, daytype_id, stop_id) VALUES(?,?,?,?)", (bus_id, row[3], 2, laststop_id))
            if row[4] is not None:
                con.execute("INSERT INTO times(bus_id, time, daytype_id, stop_id) VALUES(?,?,?,?)", (bus_id, row[4], 3, firststop_id))
            if row[5] is not None:
                con.execute("INSERT INTO times(bus_id, time, daytype_id, stop_id) VALUES(?,?,?,?)", (bus_id, row[5], 3, laststop_id))
        con.commit()
if __name__ == "__main__":
    main()
